Hello,
I am working on a project and trying to count how often certain text strings occur in cells.
If the values were numbers, I could use the frequency function, however it doesn't seem to work with text. Is there another function I could use or a way around this?
iPad, iOS 5.1
Hi Reddkryten,
COUNTIF() could work for you. If there might be other text before or after start or end your search string with a "*" i.e. "thurs*" will find "Thursday".
More info would be helpful. Can you provide a screenshot?
quinn
Reddkryten wrote:
trying to count how often certain text strings occur in cells.
If you need a count of how often a string occurs in cell(s) and it may occur more than once within a cell, then you can do something like this:
The formula in B2, copied down the column is:
=(LEN(A2)−LEN(SUBSTITUTE(A2,B$1,"")))÷LEN(B$1)
This compares the original length of the source text with the length of the text when the search string is removed from it (by substituting "" or nothing for the search string) and then divides by the length of the search string to derive how often the search string occurs in the source text.
If, on the other hand, you simply need a count of the cells that contain the search string (perhaps more than once) then you can use COUNTIF. In C5 I have this:
=COUNTIF(A,"*text*")
Note that the last row containing the formulas is defined as a Footer Row, allowing the use of A and B to refer to the entire column in the formulas.
Illustrations are from Numbers for the Mac but the formulas are the same in Numbers for iOS.
SG
Search for frequency in text strings
